unit uApontFiltro;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Grids, DBGrids, StdCtrls, Buttons, ComCtrls, ExtCtrls, FMTBcd,
  DB, DBClient, Provider, SqlExpr, Mask, JvExMask, JvToolEdit, JvMaskEdit,
  JvCheckedMaskEdit, JvDatePickerEdit, JvSpin, rpcompobase, rpvclreport,
  JvBaseEdits;

type
  TfApontFiltro = class(TForm)
    Panel1: TPanel;
    Label1: TLabel;
    edOF: TEdit;
    Label2: TLabel;
    edOFDet: TEdit;
    Label3: TLabel;
    edProd: TEdit;
    Label4: TLabel;
    btnProcurar: TBitBtn;
    Panel2: TPanel;
    Panel3: TPanel;
    DBGrid1: TDBGrid;
    sdsFiltro: TSQLDataSet;
    dspFiltro: TDataSetProvider;
    cdsFiltro: TClientDataSet;
    dsFiltro: TDataSource;
    sdsFiltroAPONT: TFMTBCDField;
    sdsFiltroETIQUETA: TStringField;
    sdsFiltroOF_DET: TFMTBCDField;
    sdsFiltroDATA: TSQLTimeStampField;
    sdsFiltroQTDE: TFMTBCDField;
    sdsFiltroUSUARIO: TStringField;
    sdsFiltroCOLETOR: TStringField;
    sdsFiltroTIPO: TStringField;
    sdsFiltroO_F: TStringField;
    cdsFiltroAPONT: TFMTBCDField;
    cdsFiltroETIQUETA: TStringField;
    cdsFiltroOF_DET: TFMTBCDField;
    cdsFiltroDATA: TSQLTimeStampField;
    cdsFiltroQTDE: TFMTBCDField;
    cdsFiltroUSUARIO: TStringField;
    cdsFiltroCOLETOR: TStringField;
    cdsFiltroTIPO: TStringField;
    cdsFiltroO_F: TStringField;
    sdsFiltroCOF_CODPRO: TStringField;
    cdsFiltroCOF_CODPRO: TStringField;
    Label5: TLabel;
    sqr: TSQLQuery;
    Label6: TLabel;
    edColetor: TEdit;
    Label7: TLabel;
    edUsuario: TEdit;
    sdsFiltroPRO_DESCRI: TStringField;
    sdsFiltroCOL_MODELO: TStringField;
    sdsFiltroUSU_NOMUSU: TStringField;
    cdsFiltroPRO_DESCRI: TStringField;
    cdsFiltroCOL_MODELO: TStringField;
    cdsFiltroUSU_NOMUSU: TStringField;
    jvDate1: TJvDatePickerEdit;
    jvDate2: TJvDatePickerEdit;
    BitBtn7: TBitBtn;
    Panel4: TPanel;
    DBGrid2: TDBGrid;
    sdsF: TSQLDataSet;
    dspF: TDataSetProvider;
    cdsF: TClientDataSet;
    dsF: TDataSource;
    sdsFOF_DET: TFMTBCDField;
    sdsFQTDE: TFMTBCDField;
    sdsFO_F: TStringField;
    cdsFOF_DET: TFMTBCDField;
    cdsFQTDE: TFMTBCDField;
    cdsFO_F: TStringField;
    btnSair: TBitBtn;
    sdsFPRO_CODPRO: TStringField;
    sdsFPRO_DESCRI: TStringField;
    cdsFPRO_CODPRO: TStringField;
    cdsFPRO_DESCRI: TStringField;
    edTotal: TJvCalcEdit;
    t1: TEdit;
    t2: TEdit;
    procedure btnProcurarClick(Sender: TObject);
    procedure btnSairClick(Sender: TObject);
    procedure BitBtn7Click(Sender: TObject);
    procedure DBGrid1CellClick(Column: TColumn);
  private
    rep1: TVCLReport;
    { Private declarations }
  public
    str, strP, strQ, strDet: string;
    { Public declarations }
  end;

var
  fApontFiltro: TfApontFiltro;

implementation

uses udm;

{$R *.dfm}

procedure TfApontFiltro.btnProcurarClick(Sender: TObject);
begin
  strP := '';
  if (cdsF.Active) then
    cdsF.Close;
  str := 'SELECT APT_COFDET OF_Det, PRO_CODPRO, PRO_DESCRI, ';
  str := str + ' SUM(APT_QTDAPT) Qtde,  DOF_CODIOF O_F ';

  str := str + ' FROM ATS_APONTAMENTO, ATS_DETOF, ATS_OF, ATS_COLETOR_CAD, F_PRODS, F_USERS ';
  str := str + ' WHERE DOF_CODIGO = APT_COFDET AND COF_CODIOF = DOF_CODIOF ' ;
  str := str + ' AND COL_NSERIE(+) = APT_COLETO AND COF_CODPRO = PRO_CODPRO ';
  str := str + ' AND USU_LOGUSU(+) = APT_CODUSU ';

  //strP := ' AND APT_DATAPT BETWEEN ' + QuotedStr(FormatDateTime('dd/MM/yyyy hh:mm',dt1.DateTime));
  //strP := strP + ' AND ' + QuotedStr(FormatDateTime('dd/MM/yyyy hh:mm',dt2.DateTime));
  strP := strP +  ' AND APT_DATAPT BETWEEN  ' +
    'to_date(' + QuotedStr(FormatDateTime('dd/MM/yyyy ',jvDate1.Date) + FormatDateTime('HH:mm',StrToTime(t1.Text))) + ',' + QuotedStr('dd/MM/yyyy HH24:mi') + ')';
  strP := strP + ' AND ' +
    'to_date(' + QuotedStr(FormatDateTime('dd/MM/yyyy ',jvDate2.Date) + FormatDateTime('HH:mm',StrToTime(t2.Text))) + ',' + QuotedStr('dd/MM/yyyy HH24:mi') + ')';

  if (edOf.Text <> '') then
  begin
    strP := strP + ' AND DOF_CODIOF = ' + QuotedStr(edOf.Text);
  end;

  if (edOFDet.Text <> '') then
  begin
    strP := strP + ' AND APT_COFDET = ' + edOFDet.Text;
  end;

  if (edProd.Text <> '') then
  begin
    strP := strP + ' AND COF_CODPRO = ' + QuotedStr(edProd.Text);
  end;

  if (edUsuario.Text <> '') then
  begin
    strP := strP + ' AND APT_CODUSU = ' + QuotedStr(edUsuario.Text);
  end;

  if (edColetor.Text <> '') then
  begin
    strP := strP + ' AND APT_COLETO = ' + QuotedStr(edColetor.Text);
  end;


  cdsF.CommandText := str + strP + ' GROUP BY APT_COFDET, DOF_CODIOF, PRO_CODPRO, PRO_DESCRI ';
  cdsF.Open;

  strQ := 'SELECT sum(APT_QTDAPT) Qtde';
  strQ := strQ + ' FROM ATS_APONTAMENTO, ATS_DETOF, ATS_OF ';
  strQ := strQ + ' WHERE DOF_CODIGO = APT_COFDET AND COF_CODIOF = DOF_CODIOF ' ;

  if (sqr.Active) then
    sqr.Close;

  sqr.SQL.Clear;
  sqr.SQL.Add(strQ + strP);
  sqr.Open;

  if (not sqr.IsEmpty) then
    edTotal.Text := FloatToStr(sqr.Fields[0].AsFloat);

  sqr.Close;
end;

procedure TfApontFiltro.btnSairClick(Sender: TObject);
begin
  Close;
end;

procedure TfApontFiltro.BitBtn7Click(Sender: TObject);
begin
    rep1 := TVCLReport.Create(self);
    //inherited;
    rep1.FileName := 'Apontamento.rep';
    rep1.Report.DatabaseInfo.Items[0].SQLConnection := dm.SQLConnection1;
    rep1.Report.DataInfo.Items[1].SQL := strDet + strP +' AND DOF_CODIGO = ' +
      IntToStr(cdsFOF_DET.AsInteger) ;
    Rep1.Title := Rep1.FileName;
    Rep1.Execute;
    Rep1.Destroy;

end;

procedure TfApontFiltro.DBGrid1CellClick(Column: TColumn);
begin
  if (cdsFiltro.Active) then
    cdsFiltro.Close;

  strDet := 'SELECT APT_CODIGO Apont, APT_CODETI Etiqueta, APT_COFDET OF_Det, ';
  strDet := strDet + ' APT_DATAPT Data, APT_QTDAPT Qtde, APT_CODUSU Usuario, APT_COLETO ';
  strDet := strDet + ' Coletor, APT_TIPAPT Tipo, DOF_CODIOF O_F, COF_CODPRO';
  strDet := strDet + ' ,PRO_DESCRI, COL_MODELO, USU_NOMUSU ';
  strDet := strDet + ' FROM ATS_APONTAMENTO, ATS_DETOF, ATS_OF, ATS_COLETOR_CAD, F_PRODS, F_USERS ';
  strDet := strDet + ' WHERE DOF_CODIGO = APT_COFDET AND COF_CODIOF = DOF_CODIOF ' ;
  strDet := strDet + ' AND COL_NSERIE(+) = APT_COLETO AND COF_CODPRO = PRO_CODPRO ';
  strDet := strDet + ' AND USU_LOGUSU(+) = APT_CODUSU ';
  cdsFiltro.CommandText := strDet + strP + ' AND DOF_CODIGO = ' + IntToStr(cdsFOF_DET.AsInteger) ;
  cdsFiltro.Open;

end;


end.


